import pymysql
import pandas as pd
import numpy as np
from sklearn import linear_model
from sklearn.naive_bayes import GaussianNB
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from sklearn.model_selection import train_test_split

def show_linear_line(X_parameters, Y_parameters):
	# regr = linear_model.LinearRegression()
	regr = GaussianNB()
	regr.fit(X_parameters, Y_parameters)
	plt.scatter(X_parameters, Y_parameters, color='blue')
	plt.plot(X_parameters, regr.predict(X_parameters), color='red', linewidth=4)
	plt.xticks(())
	plt.yticks(())
	plt.show()
# def show_oridata(show_date):
#     fig = plt.figure(1, figsize=(8, 6))
#     ax = Axes3D(fig, elev=-150, azim=110)
#     ax.scatter(show_date[:, 0], show_date[:, 1], show_date[:, 2], edgecolor='k', s=40)
#     plt.show()

'''
查询数据库某一列保存为list
 '''
conn = pymysql.connect(host='localhost', user='root', passwd="HxP1232061", db="lottery", port=3306,
		                       charset='utf8')
cs1 = conn.cursor()
# 11个参数，预测年份
input1 = 'doublecolordata1'
input2 = 'numS'
input3 = 'redball1'
input4 = 'redball2'
input5 = 'redball3'
input6 = 'redball4'
input7 = 'redball5'
input8 = 'redball6'
input9 = 'blueball'
input10 = 20004
input11 = 'number'

# 读取一列数据
sql2 = "select (%s) from (%s); " % (input11, input1)
# sql1 = "select (%s) from (%s); " % (input2, input1)
# cs1.execute(sql1)
# datalist1 = []
# alldata1 = cs1.fetchall()
# for s in alldata1:
# 	datalist1.append(s[0])
# print(datalist1)
# 读取多列数据放入pandas
sql1 = "select (%s),(%s),(%s),(%s),(%s),(%s),(%s) from (%s); " % (input3,input4,input5,input6,input7,input8,input9,input1)
# a=pd.read_sql(sql1,conn)
# print(a[0:100])
# print(a.describe())
# # 读取第一列redball1
# sql2 = "select (%s) from (%s); " % (input3, input1)
# cs1.execute(sql2)
# datalist2 = []
# alldata2 = cs1.fetchall()
# for s in alldata2:
# 	datalist2.append(s[0])
# print(datalist2)
# # 新版的sklearn中，所有的数据都应该是二维矩阵，哪怕它只是单独一行或一列（比如前面做预测时，仅仅只用了一个样本数据），所以需要使用.reshape(1,-1)进行转换
# datalist11=np.array(datalist1).reshape(len(datalist1),-1)
X_data = pd.read_sql(sql1,conn)

# # datalist11 = []
# # for i in datalist1:
# # 	list1 = [i]
# # 	datalist11.append(list1)
# datalist22=np.array(datalist2).reshape(len(datalist1),-1)
Y_data = pd.read_sql(sql2,conn)
#数据预处理
X_train, X_test, y_train, y_test = train_test_split(X_data, Y_data, test_size=0.3, random_state=42)
print(X_train)
print(X_test)
print(y_train)
print(y_test)

# show_linear_line(datalist11['blueball'],datalist2['number'])
# # 回归预测
# regr = linear_model.LinearRegression(fit_intercept=True, normalize=False)
regr = GaussianNB()
# regr.fit(X_train, y_train)
regr.fit(Y_data, X_data)
# datalist12 = []
# datalist12.append(input10)
# datalist12 = np.array(datalist12).reshape(-1, 1)
# output_predictvalue = regr.predict(datalist12)
# print(output_predictvalue)
# y_pred = regr.predict(X_train).round()
# y_pred2 = regr.predict(X_test).round()
# print(y_pred2)
# print(y_pred)
y_pred = regr.predict(Y_data).round()
print(y_pred)
# y_pred_cache = list()
# for line in y_pred :
# 	# line = list(line)
# 	# if line not in y_pred_cache:
# 	y_pred_cache.append(line)
# for line in y_pred_cache:
# 	print(line)